package com.hsnn.medstgmini.subscribe.dao.impl;

import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.pro.project.model.BidProject;
import com.hsnn.medstgmini.subscribe.dao.BidSubscribeDao;
import com.hsnn.medstgmini.subscribe.dao.BidSubscribeDetailDao;
import com.hsnn.medstgmini.subscribe.model.BidSubscribe;
import com.hsnn.medstgmini.subscribe.model.BidSubscribeDetail;
import com.hsnn.medstgmini.util.FormatDate;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 预约DAO接口实现类
 * @author 韩守松
 * @date 2015年8月27日
 */
@Repository
public class BidSubscribeDaoImpl extends BaseHibernateDAO implements BidSubscribeDao{
	
	@Autowired
	private BidSubscribeDetailDao BidSubscribeDetailDao;
	/**
	 * @category 预约设置保存
	 * @author 韩守松
	 * @date   2015年8月28日
	 * @param  @param bidSubscribe
	 */
	public void save(BidSubscribe bidSubscribe) {
		bidSubscribe.setBidSubscribeId(UUID.randomUUID().toString());
		getSession().saveOrUpdate(bidSubscribe);
		getSession().flush();
		saveLog(bidSubscribe);
		if(bidSubscribe.getIsEnable() == 1){
			saveDetail(bidSubscribe);
		}
	}
	
	/**
	 * @category 获取预约信息
	 * @author 韩守松
	 * @date   2015年8月28日
	 * @param  @param page
	 * @param  @param bidSubscribe
	 * @param  @return
	 */
	public Pagination getList(Pagination page,BidSubscribe bidSubscribe) {
		Date  subStartDate = bidSubscribe.getSubStartDate();
		Date  subEndDate = bidSubscribe.getSubEndDate();
		Integer includeWeekend = bidSubscribe.getIncludeWeekend();
		StringBuffer hql = new StringBuffer();
		StringBuffer countHql = new StringBuffer();
		hql.append("select ");
		hql.append("bs.bid_subscribe_id bidSubscribeId,");
		hql.append("bs.proj_id projId,bp.proj_code projCode,DATE_FORMAT(bp.starttime,'%Y-%m-%d') starttime,");
		hql.append("bp.name as projName,DATE_FORMAT(bp.endtime,'%Y-%m-%d') endtime,bp.status projStatus,");
		hql.append("DATE_FORMAT(bs.sub_start_date,'%Y-%m-%d') subStartDateStr,");
		hql.append("DATE_FORMAT(bs.sub_end_date,'%Y-%m-%d') subEndDateStr,");
		hql.append("bs.include_weekend includeWeekend ,");
		hql.append("DATE_FORMAT(bs.process_start_time_one,'%H:%i') processStartTimeOneStr,");
		hql.append("DATE_FORMAT(bs.process_end_time_one,'%H:%i') processEndTimeOneStr,");
		hql.append("DATE_FORMAT(bs.process_start_time_two,'%H:%i') processStartTimeTwoStr,");
		hql.append("DATE_FORMAT(bs.process_end_time_two,'%H:%i') processEndTimeTwoStr,");
		hql.append("DATE_FORMAT(bs.process_start_time_three,'%H:%i') processStartTimeThreeStr,");
		hql.append("DATE_FORMAT(bs.process_end_time_three,'%H:%i') processEndTimeThreeStr,");
		hql.append("bs.process_unit_one processUnitOne,");
		hql.append("bs.person_count_one personCountOne,");
		hql.append("bs.process_unit_two processUnitTwo,");
		hql.append("bs.person_count_two personCountTwo,");
		hql.append("bs.process_unit_three processUnitThree,");
		hql.append("(select count(*) from bid_subscribe_record bsr where bsr.bid_subscribe_detail_id in ( select bsd.bid_subscribe_detail_id from bid_subscribe_detail bsd where bsd.bid_subscribe_id = bs.bid_subscribe_id and bsd.subscribe_date >= bs.sub_start_date and bsd.subscribe_date <= bs.sub_end_date and bs.process_start_time_one <= bsd.subscribe_starttime and bs.process_end_time_one >= bsd.subscribe_endtime) ) as personNumOne,"); 
		hql.append("(select count(*) from bid_subscribe_record bsr where bsr.bid_subscribe_detail_id in ( select bsd.bid_subscribe_detail_id from bid_subscribe_detail bsd where bsd.bid_subscribe_id = bs.bid_subscribe_id and bsd.subscribe_date >= bs.sub_start_date and bsd.subscribe_date <= bs.sub_end_date and bs.process_start_time_two <= bsd.subscribe_starttime and bs.process_end_time_two >= bsd.subscribe_endtime) ) as personNumTwo,"); 
		hql.append("bs.person_count_three personCountThree,");
		hql.append("bs.status,");
		hql.append("bs.remarks,");
		hql.append(" bs.is_enable isEnable, ");
		hql.append(" bs.center_address centerAddress, ");
		hql.append("DATE_FORMAT(bs.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetimeStr,");
		hql.append("bs.upd_user updUser ");
		hql.append("from bid_subscribe bs ");
		hql.append("left join bid_project bp on bs.proj_id = bp.proj_id where bs.is_enable=1 ");
		countHql.append("select count(*) from bid_subscribe bs ");
		countHql.append("left join bid_project bp on bs.proj_id = bp.proj_id where bs.is_enable=1 ");
		
		if(StringUtils.isNotBlank(bidSubscribe.getProjId())){
			hql.append(" and bs.proj_id = :projId ");
			countHql.append(" and bs.proj_id = :projId ");
		}
		if(null != includeWeekend){
			hql.append(" and bs.include_weekend = :includeWeekend ");
			countHql.append(" and bs.include_weekend = :includeWeekend ");
		}
		if(null != subStartDate){
			hql.append(" and DATE_FORMAT(bs.sub_start_date,'%Y-%m-%d') >= :subStartDate ");
			countHql.append(" and DATE_FORMAT(bs.sub_start_date,'%Y-%m-%d') >= :subStartDate ");
		}
		if(null != subEndDate){
			hql.append(" and DATE_FORMAT(bs.sub_end_date,'%Y-%m-%d') <= :subEndDate ");
			countHql.append(" and DATE_FORMAT(bs.sub_end_date,'%Y-%m-%d') <= :subEndDate ");
		}
		hql.append(" order by bs.upd_datetime desc ");
		Query countQuery = getSession().createSQLQuery(countHql.toString());
		if(StringUtils.isNotBlank(bidSubscribe.getProjId())){
			countQuery.setString("projId", bidSubscribe.getProjId());
		}
		if(null != includeWeekend){
			countQuery.setInteger("includeWeekend",includeWeekend );
		}
		if(null != subStartDate){
			countQuery.setDate("subStartDate", subStartDate);
		}
		if(null != subEndDate){
			countQuery.setDate("subEndDate", subEndDate);
		}
		page.setRecords(Long.valueOf(countQuery.uniqueResult().toString()));
		Query query = getSession().createSQLQuery(hql.toString());
		if(StringUtils.isNotBlank(bidSubscribe.getProjId())){
			query.setString("projId", bidSubscribe.getProjId());
		}
		if(null != includeWeekend){
			query.setInteger("includeWeekend",includeWeekend );
		}
		if(null != subStartDate){
			query.setDate("subStartDate", subStartDate);
		}
		if(null != subEndDate){
			query.setDate("subEndDate", subEndDate);
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}
	
	

	/**
	 * @category 根据预约id 获取预约信息 
	 * @author 韩守松
	 * @date   2015年8月29日
	 * @param  @param bidSubscribeId
	 * @param  @return
	 */
	@SuppressWarnings({"rawtypes" })
	public List getBidSubscribeById(String bidSubscribeId) {
		StringBuffer hql = new StringBuffer();
		hql.append("select ");
		hql.append("bs.bid_subscribe_id bidSubscribeId,");
		hql.append("bs.proj_id projId,bp.proj_code projCode,DATE_FORMAT(bp.starttime,'%Y-%m-%d') starttime,");
		hql.append("bp.name as projName,DATE_FORMAT(bp.endtime,'%Y-%m-%d') endtime,bp.status projStatus,");
		hql.append("DATE_FORMAT(bs.sub_start_date,'%Y-%m-%d') subStartDateStr,");
		hql.append("DATE_FORMAT(bs.sub_end_date,'%Y-%m-%d') subEndDateStr,");
		hql.append("bs.include_weekend includeWeekend ,");
		hql.append("DATE_FORMAT(bs.process_start_time_one,'%H:%i') processStartTimeOneStr,");
		hql.append("DATE_FORMAT(bs.process_end_time_one,'%H:%i') processEndTimeOneStr,");
		hql.append("DATE_FORMAT(bs.process_start_time_two,'%H:%i') processStartTimeTwoStr,");
		hql.append("DATE_FORMAT(bs.process_end_time_two,'%H:%i') processEndTimeTwoStr,");
		hql.append("DATE_FORMAT(bs.process_start_time_three,'%H:%i:%S') processStartTimeThreeStr,");
		hql.append("DATE_FORMAT(bs.process_end_time_three,'%H:%i:%S') processEndTimeThreeStr,");
		hql.append("bs.process_unit_one processUnitOne,");
		hql.append("bs.person_count_one personCountOne,");
		hql.append("bs.process_unit_two processUnitTwo,");
		hql.append("bs.person_count_two personCountTwo,");
		hql.append("bs.process_unit_three processUnitThree,");
		hql.append("bs.person_count_three personCountThree,");
		hql.append("bs.status,");
		hql.append("bs.remarks,");
		hql.append(" bs.is_enable isEnable, ");
		hql.append(" bs.center_address centerAddress, ");
		hql.append("DATE_FORMAT(bs.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetimeStr,");
		hql.append("bs.upd_user updUser ");
		hql.append("from bid_subscribe bs ");
		hql.append("left join bid_project bp on bs.proj_id = bp.proj_id where 1=1 ");
		hql.append(" and bs.bid_subscribe_id = :bidSubscribeId");
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("bidSubscribeId", bidSubscribeId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List list = query.list();
		return list;
	}
	
	
	/**
	 * @category 预约设置信息编辑
	 * @author 韩守松
	 * @date   2015年8月29日
	 * @param  @param bidSubscribe
	 * @param  @return
	 */
	public Pagination update(BidSubscribe bidSubscribe) {
		Pagination page = new Pagination();
		getSession().saveOrUpdate(bidSubscribe);
		getSession().flush();
		saveLog(bidSubscribe);
		
		if(bidSubscribe.getIsEnable() == 1){
			saveDetail(bidSubscribe);
		}
		page.setSuccess(true);
		return page;
	}
	
	
	/**
	 * @category 预约设置状态 更新
	 * @author 韩守松
	 * @date   2015年8月30日
	 * @param  @param bidSubscribe
	 * @param  @return
	 */
	public Pagination statusUpdate(BidSubscribe bidSubscribe) {
		Pagination page = new Pagination();
		StringBuffer hql = new StringBuffer();
		hql.append("update  bid_subscribe  set is_enable = :isEnable ,upd_datetime= :updDatetime ,upd_user= :updUser  where bid_subscribe_id = :bidSubscribeId");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setInteger("isEnable", bidSubscribe.getIsEnable());
		query.setTimestamp("updDatetime", bidSubscribe.getUpdDatetime());
		query.setString("updUser", bidSubscribe.getUpdUser());
		query.setString("bidSubscribeId", bidSubscribe.getBidSubscribeId());
		query.executeUpdate();
		getSession().flush();
		
		getSession().clear();
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into bid_subscribe_log  ")
		   .append(" select UUID(),bs.* from bid_subscribe bs where bs.bid_subscribe_id= :bidSubscribeId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("bidSubscribeId", bidSubscribe.getBidSubscribeId());
		queryLog.executeUpdate();
		getSession().flush();
		return page;
	}
	
	/**
	 * @category 预约新建状态开启 
	 * @author 韩守松
	 * @date   2015年8月30日
	 * @param  @param bidSubscribe
	 * @param  @return
	 */
	public Pagination isEnableUpdate(BidSubscribe bidSubscribe) {
		Pagination page = new Pagination();
		StringBuffer hql = new StringBuffer();
		hql.append("update  bid_subscribe  set is_enable = :isEnable ,upd_datetime= :updDatetime ,upd_user= :updUser  where bid_subscribe_id = :bidSubscribeId");
		SQLQuery query = getSession().createSQLQuery(hql.toString());
		query.setInteger("isEnable", bidSubscribe.getIsEnable());
		query.setTimestamp("updDatetime", bidSubscribe.getUpdDatetime());
		query.setString("updUser", bidSubscribe.getUpdUser());
		query.setString("bidSubscribeId", bidSubscribe.getBidSubscribeId());
		query.executeUpdate();
		getSession().flush();
		
		getSession().clear();
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into bid_subscribe_log  ")
		   .append(" select UUID(),bs.* from bid_subscribe bs where bs.bid_subscribe_id= :bidSubscribeId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("bidSubscribeId", bidSubscribe.getBidSubscribeId());
		queryLog.executeUpdate();
		getSession().flush();
		
		BidSubscribe bidSubscribeDemo = new BidSubscribe();
		bidSubscribeDemo = (BidSubscribe)getBidSubscribeBySubscribeId(bidSubscribe.getBidSubscribeId()).get(0);
		saveDetail(bidSubscribeDemo);
		page.setSuccess(true);
		return page;
		
	}
	

	/**
	 * @category 根据预约设置id 获取预约信息
	 * @author 韩守松
	 * @date   2015年9月2日
	 * @param  @param bidSubscribeId
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public List<BidSubscribe> getBidSubscribeBySubscribeId(String bidSubscribeId) {
		StringBuffer hql = new StringBuffer();
		hql.append(" from BidSubscribe bsd where bsd.bidSubscribeId = :bidSubscribeId ");
		Query query = getSession().createQuery(hql.toString());
		query.setString("bidSubscribeId", bidSubscribeId);
		List<BidSubscribe> list = query.list();
		return list;
	}
	
	/**
	 * @category 获取预约项目信息
	 * @author 韩守松
	 * @date   2015年9月6日
	 * @param  @param page
	 * @param  @param bidProject
	 * @param  @return
	 */
	public Pagination getProjList(Pagination page,BidProject bidProject){
		StringBuffer hql = new StringBuffer();
		StringBuffer countHql = new StringBuffer();
		hql.append("  select ");
		hql.append("  bp.proj_id projId, ");
		hql.append("  bp.proj_code projCode, ");
		hql.append("  bp.name as projName, ");
		hql.append("  DATE_FORMAT(bp.starttime,'%Y-%m-%d') starttime, ");
		hql.append("  DATE_FORMAT(bp.endtime,'%Y-%m-%d') endtime, ");
		hql.append("  bs.bid_subscribe_id bidSubscribeId, ");
		hql.append("  DATE_FORMAT(bs.sub_start_date,'%Y-%m-%d') subStartDate, ");
		hql.append("  DATE_FORMAT(bs.sub_end_date,'%Y-%m-%d') subEndDate, ");
		hql.append(" (select count(*) from bid_subscribe bs where bp.proj_id = bs.proj_id) isSet, ");
		hql.append(" bp.status projStatus, ");
		hql.append(" bs.is_enable isEnable, ");
		hql.append(" bs.center_address centerAddress,DATE_FORMAT(bpd.proj_starttime,'%Y-%m-%d') projStarttime,DATE_FORMAT(bpd.proj_endtime,'%Y-%m-%d') projEndtime ");
		hql.append(" from bid_project bp ");
		hql.append(" left join bid_subscribe bs on bp.proj_id = bs.proj_id ");
		hql.append(" left join bid_project_details bpd on bp.proj_id = bpd.proj_id ");
		hql.append("  where 1=1");
	
		countHql.append(" select count(*) from bid_project bp   left join bid_subscribe bs on bp.proj_id = bs.proj_id left join bid_project_details bpd on bp.proj_id = bpd.proj_id where 1=1 ");
		
		if(StringUtils.isNotBlank(bidProject.getProjId())){
			hql.append(" and bp.proj_id = :projId ");
			countHql.append(" and bp.proj_id = :projId ");
		}
		
		if(StringUtils.isNotBlank(bidProject.getProjCode())){
			hql.append(" and bp.proj_code = :projCode ");
			countHql.append(" and bp.proj_code = :projCode ");
		}
		if(StringUtils.isNotBlank(bidProject.getName())){
			hql.append(" and bp.name like :projName ");
			countHql.append(" and bp.name like :projName ");
		}
		hql.append(" order by bs.sub_start_date desc,bs.upd_datetime desc ");
		Query countQuery = getSession().createSQLQuery(countHql.toString());
		if(StringUtils.isNotBlank(bidProject.getProjId())){
			countQuery.setString("projId", bidProject.getProjId());
		}
		if(StringUtils.isNotBlank(bidProject.getProjCode())){
			countQuery.setString("projCode", bidProject.getProjCode());
		}
		if(StringUtils.isNotBlank(bidProject.getName())){
			countQuery.setString("projName", "%"+bidProject.getName()+"%");
		}
		page.setRecords(Long.valueOf(countQuery.uniqueResult().toString()));
		Query query = getSession().createSQLQuery(hql.toString());
		if(StringUtils.isNotBlank(bidProject.getProjId())){
			query.setString("projId", bidProject.getProjId());
		}
		if(StringUtils.isNotBlank(bidProject.getProjCode())){
			query.setString("projCode", bidProject.getProjCode());
		}
		if(StringUtils.isNotBlank(bidProject.getName())){
			query.setString("projName", "%"+bidProject.getName()+"%");
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}
	public void saveLog(BidSubscribe bidSubscribe){
		
		getSession().clear();
		String bidSubscribeId = bidSubscribe.getBidSubscribeId();
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into bid_subscribe_log  ")
		   .append(" select UUID(),sc.* from bid_subscribe sc where sc.bid_subscribe_id= :bidSubscribeId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("bidSubscribeId", bidSubscribeId);
		queryLog.executeUpdate();
		getSession().flush();
		/*BidSubscribeLog bidSubscribeLog = new BidSubscribeLog();
			bidSubscribeLog.setLogId(UUID.randomUUID().toString());
			bidSubscribeLog.setBidSubscribeId(bidSubscribe.getBidSubscribeId());
			bidSubscribeLog.setSubStartDate(bidSubscribe.getSubStartDate());
			bidSubscribeLog.setSubEndDate(bidSubscribe.getSubEndDate());
			bidSubscribeLog.setIncludeWeekend(bidSubscribe.getIncludeWeekend());
			bidSubscribeLog.setIsEnable(bidSubscribe.getIsEnable());
			bidSubscribeLog.setCenterAddress(bidSubscribe.getCenterAddress());
			bidSubscribeLog.setProcessStartTimeOne(bidSubscribe.getProcessStartTimeOne());
			bidSubscribeLog.setProcessEndTimeOne(bidSubscribe.getProcessEndTimeOne());
			bidSubscribeLog.setProcessStartTimeTwo(bidSubscribe.getProcessStartTimeTwo());
			bidSubscribeLog.setProcessEndTimeTwo(bidSubscribe.getProcessEndTimeTwo());
			bidSubscribeLog.setProcessStartTimeThree(bidSubscribe.getProcessEndTimeThree());
			bidSubscribeLog.setProcessEndTimeThree(bidSubscribe.getProcessEndTimeThree());
			bidSubscribeLog.setProcessUnitOne(bidSubscribe.getProcessUnitOne());
			bidSubscribeLog.setProcessUnitTwo(bidSubscribe.getProcessUnitTwo());
			bidSubscribeLog.setProcessUnitThree(bidSubscribe.getProcessUnitThree());
			bidSubscribeLog.setPersonCountOne(bidSubscribe.getPersonCountOne());
			bidSubscribeLog.setPersonCountTwo(bidSubscribe.getPersonCountTwo());
			bidSubscribeLog.setPersonCountThree(bidSubscribe.getPersonCountThree());
			bidSubscribeLog.setStatus(bidSubscribe.getStatus());
			bidSubscribeLog.setRemarks(bidSubscribe.getRemarks());
			bidSubscribeLog.setUpdDatetime(bidSubscribe.getUpdDatetime());
			bidSubscribeLog.setUpdUser(bidSubscribe.getUpdUser());
			getSession().saveOrUpdate(bidSubscribeLog);
			getSession().flush();*/
	}

	/**
	 * @category 按单位存储时间段 存储时间段
	 * @author 韩守松
	 * @date   2015年8月30日
	 * @param  @param bidSubscribe
	 */
	public void saveDetail(BidSubscribe bidSubscribe){
		
		Integer inculdel=bidSubscribe.getIncludeWeekend();//是否包含周末
		Date subStartDate = bidSubscribe.getSubStartDate();//预约开始日期
		Date subEndDate = bidSubscribe.getSubEndDate();//预约结束日期
		
	
		
		Date processStartTimeOne = bidSubscribe.getProcessStartTimeOne();//	预约上午时间段开始
		Date processEndTimeOne = bidSubscribe.getProcessEndTimeOne();//预约上午时间段结束
		Integer processUnitOne = bidSubscribe.getProcessUnitOne();//预约上午时间段单位
		Integer personCountOne = bidSubscribe.getPersonCountOne();//预计上午每个时间单位人数
		
		Date processStartTimeTwo = bidSubscribe.getProcessStartTimeTwo();//预约下午时间段开始
		Date processEndTimeTwo = bidSubscribe.getProcessEndTimeTwo();//预约下午时间段结束
		Integer processUnitTwo = bidSubscribe.getProcessUnitTwo();//预约下午时间段单位
		Integer personCountTwo = bidSubscribe.getPersonCountTwo();//预计下午午每个时间单位人数
		
		Integer dateNumber = 0;
		Calendar calendar = Calendar.getInstance();
		
		while(true){
			Long subStart = subStartDate.getTime();
			Long subEnd  = subEndDate.getTime();
			Date middleDate = subStartDate;
			if(subStart <= subEnd){
				//时间段一是否包含周末
				if(inculdel == 1){//包含周末
					calendar.setTime(subStartDate);
					dateNumber = calendar.get(Calendar.MINUTE) + 24*60 ;
					calendar.set(Calendar.MINUTE, dateNumber);
					subStartDate = calendar.getTime();
				}else{
					calendar.setTime(subStartDate);
					String week=String.valueOf(calendar.get(Calendar.DAY_OF_WEEK)-1);
					if("6".equals(week)){
						dateNumber = calendar.get(Calendar.MINUTE) + 24*2*60 ;
						calendar.set(Calendar.MINUTE, dateNumber);
						subStartDate = calendar.getTime();
						continue;
					}
					if("0".equals(week)){
						dateNumber = calendar.get(Calendar.MINUTE) + 24*60 ;
						calendar.set(Calendar.MINUTE, dateNumber);
						subStartDate = calendar.getTime();
						continue;
					}
					
					middleDate=subStartDate;
					if(!"6".equals(week) && !"0".equals(week)){
						dateNumber = calendar.get(Calendar.MINUTE) + 24*60 ;
						calendar.set(Calendar.MINUTE, dateNumber);
						subStartDate = calendar.getTime();
					}
					
				}
				
			}else{
				break;
			}
			if(null != processStartTimeOne && null != processEndTimeOne ){
				Date subscribeStarttime = new Date();//初始化subscribeStarttime
				Integer mine = 0 ;
				Date subscribeEndtime = processStartTimeOne;//初始化subscribeEndtime
				while(subscribeEndtime.getTime() <  processEndTimeOne.getTime()){
					subscribeStarttime = subscribeEndtime;
					calendar.setTime(subscribeEndtime);
					mine = calendar.get(Calendar.MINUTE)+processUnitOne;
					calendar.set(Calendar.MINUTE, mine);
					subscribeEndtime = calendar.getTime();
				/*	
					BidSubscribeDetail  bidSubscribeDetail = new BidSubscribeDetail();
					bidSubscribeDetail.setBidSubscribeId(bidSubscribe.getBidSubscribeId());
					bidSubscribeDetail.setSubscribeDate(middleDate);
					bidSubscribeDetail.setSubscribeStarttime(subscribeStarttime);
					bidSubscribeDetail.setSubscribeEndtime(subscribeEndtime);
					bidSubscribeDetail.setUpdUser(bidSubscribe.getUpdUser());
					bidSubscribeDetail.setUpdDatetime(bidSubscribe.getUpdDatetime());
					BidSubscribeDetailDao.save(bidSubscribeDetail);*/
					String uuId = UUID.randomUUID().toString();
					StringBuffer hql = new StringBuffer();
					hql.append("insert into bid_subscribe_detail values( ");
					hql.append("'"+uuId+"'").append(",");
					hql.append("'"+bidSubscribe.getBidSubscribeId()+"'").append(",");
					hql.append("'"+FormatDate.toShortFormat(middleDate)+"'").append(",");
					hql.append("'"+FormatDate.toTimeFormat(subscribeStarttime)+"'").append(",");
					hql.append("'"+FormatDate.toTimeFormat(subscribeEndtime)+"'").append(",");
					hql.append(0).append(",");
					hql.append(personCountOne).append(",");
					hql.append(1).append(",");
					hql.append("'"+"'").append(",");
					hql.append("'"+FormatDate.toFullFormat(bidSubscribe.getUpdDatetime())+"'").append(",");
					hql.append("'"+bidSubscribe.getUpdUser()+"'");
					hql.append(")");
					Query query = getSession().createSQLQuery(hql.toString());
					query.executeUpdate();
					getSession().flush();
					
					getSession().clear();
					StringBuffer hqlLog = new StringBuffer();
					hqlLog.append("insert into bid_subscribe_detail_log  ")
					   .append(" select UUID(),sc.* from bid_subscribe_detail sc where sc.bid_subscribe_detail_id= :uuId ");
					Query queryLog = getSession().createSQLQuery(hqlLog.toString());
					queryLog.setString("uuId", uuId);
					queryLog.executeUpdate();
					getSession().flush();
					
				}
			}
			
			if(null != processStartTimeTwo && null != processEndTimeTwo ){
				Date subscribeStarttime = new Date();//初始化subscribeStarttime
				Integer mine = 0 ;
				Date subscribeEndtime = processStartTimeTwo;//初始化subscribeEndtime
				while(subscribeEndtime.getTime() <  processEndTimeTwo.getTime()){
					subscribeStarttime = subscribeEndtime;
					calendar.setTime(subscribeEndtime);
					mine = calendar.get(Calendar.MINUTE)+processUnitTwo;
					calendar.set(Calendar.MINUTE, mine);
					subscribeEndtime = calendar.getTime();
					/*BidSubscribeDetail  bidSubscribeDetail = new BidSubscribeDetail();
					bidSubscribeDetail.setBidSubscribeId(bidSubscribe.getBidSubscribeId());
					bidSubscribeDetail.setSubscribeDate(middleDate);
					bidSubscribeDetail.setSubscribeStarttime(subscribeStarttime);
					bidSubscribeDetail.setSubscribeEndtime(subscribeEndtime);
					bidSubscribeDetail.setUpdUser(bidSubscribe.getUpdUser());
					bidSubscribeDetail.setUpdDatetime(bidSubscribe.getUpdDatetime());
						BidSubscribeDetailDao.save(bidSubscribeDetail);*/
					String uuId = UUID.randomUUID().toString();
					StringBuffer hql = new StringBuffer();
					hql.append("insert into bid_subscribe_detail values( ");
					hql.append("'"+uuId+"'").append(",");
					hql.append("'"+bidSubscribe.getBidSubscribeId()+"'").append(",");
					hql.append("'"+FormatDate.toShortFormat(middleDate)+"'").append(",");
					hql.append("'"+FormatDate.toTimeFormat(subscribeStarttime)+"'").append(",");
					hql.append("'"+FormatDate.toTimeFormat(subscribeEndtime)+"'").append(",");
					hql.append(0).append(",");
					hql.append(personCountTwo).append(",");
					hql.append(1).append(",");
					hql.append("'"+"'").append(",");
					hql.append("'"+FormatDate.toFullFormat(bidSubscribe.getUpdDatetime())+"'").append(",");
					hql.append("'"+bidSubscribe.getUpdUser()+"'");
					hql.append(")");
					Query query = getSession().createSQLQuery(hql.toString());
					query.executeUpdate();
					getSession().flush();
					
					getSession().clear();
					StringBuffer hqlLog = new StringBuffer();
					hqlLog.append("insert into bid_subscribe_detail_log  ")
					   .append(" select UUID(),sc.* from bid_subscribe_detail sc where sc.bid_subscribe_detail_id= :uuId ");
					Query queryLog = getSession().createSQLQuery(hqlLog.toString());
					queryLog.setString("uuId", uuId);
					queryLog.executeUpdate();
					getSession().flush();
				}
			  }
		}
	}
	
	/**
	  * @category 获取预约设置时间段上限人数
	  * @author 韩守松
	  * @date   2015年9月7日
	  * @param  @param bidSubscribe
	  * @param  @return
	  */
	 @SuppressWarnings("unchecked")
	public Integer getUntilNumber(BidSubscribe bidSubscribe){
		 Integer untilNumber = 0; 
		 Date startTime = bidSubscribe.getProcessStartTimeThree();
		 Date endTime = bidSubscribe.getProcessEndTimeThree();
		 StringBuffer hql = new StringBuffer();
		 hql.append(" from BidSubscribe bs where bs.bidSubscribeId = :bidSubscribeId ");
		 
		 Query query = getSession().createQuery(hql.toString());
		 query.setString("bidSubscribeId", bidSubscribe.getBidSubscribeId());
		 List<BidSubscribe> list = query.list();
		 
		 BidSubscribe bidSubscribeResult = new BidSubscribe();
		 bidSubscribeResult = list.get(0);
		 Date startTimeOne = bidSubscribeResult.getProcessStartTimeOne();
		 Date endTimeOne = bidSubscribeResult.getProcessEndTimeOne();
		 
		 Date startTimeTwo = bidSubscribeResult.getProcessStartTimeTwo();
		 Date endTimeTwo = bidSubscribeResult.getProcessEndTimeTwo();
		 
		 if(startTime.getTime() >= startTimeOne.getTime() && endTime.getTime() <= endTimeOne.getTime()  ){
			 untilNumber = bidSubscribeResult.getPersonCountOne();
		 }
		 if(startTime.getTime() >= startTimeTwo.getTime() && endTime.getTime() <= endTimeTwo.getTime()){
			 untilNumber = bidSubscribeResult.getPersonCountTwo();
		 }
		 return untilNumber;
	 }
	 
	 /**
	  * @category 获取预约企业列表信息
	  * @author 韩守松
	  * @date   2015年9月7日
	  * @param  @param bidSubscribeDetail
	  * @param  @return
	  */
	 public Pagination getSubCompList(BidSubscribeDetail bidSubscribeDetail) {
		Pagination page = new Pagination(); 
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();
		hql.append(" select  ");
		hql.append(" bsr2.comp_id compId,sc.comp_name compName, ");
		hql.append("  DATE_FORMAT(bsd2.subscribe_date,'%Y-%m-%d') subscribeDate, ");
		hql.append(" bsd2.subscribe_starttime subscribeStarttimeStr, ");
		hql.append(" bsd2.subscribe_endtime subscribeEndtimeStr ");
		hql.append(" from bid_subscribe_record bsr2 ");
		hql.append(" join bid_subscribe_detail bsd2 on bsr2.bid_subscribe_detail_id = bsd2.bid_subscribe_detail_id ");
		hql.append(" join ");
		hql.append(" (select  ");
		hql.append(" bsr.bid_subscribe_detail_id ");
		hql.append(" from bid_subscribe_record bsr ");
		hql.append(" where bsr.bid_subscribe_detail_id in ");
		hql.append(" (select ");
		hql.append(" bsd.bid_subscribe_detail_id ");
		hql.append(" from bid_subscribe_detail bsd ");
		hql.append(" left join bid_subscribe bs on bsd.bid_subscribe_id = bs.bid_subscribe_id ");
		hql.append(" where bsd.bid_subscribe_id = :bidSubscribeId ");
		hql.append(" and bsd.subscribe_date >= bs.sub_start_date and bsd.subscribe_date <= bs.sub_end_date ");
		hql.append(" and bsd.subscribe_starttime >= bs.process_start_time_one ");
		hql.append(" and bsd.subscribe_endtime <= bs.process_end_time_one)) demo ");
		hql.append(" on bsr2.bid_subscribe_detail_id = demo.bid_subscribe_detail_id ");
		hql.append(" left join std_compinfo sc on bsr2.comp_id = sc.comp_id where 1=1 ");
		
		hqlCount.append(" select  ");
		hqlCount.append(" count(*) ");
		hqlCount.append(" from bid_subscribe_record bsr2 ");
		hqlCount.append(" join bid_subscribe_detail bsd2 on bsr2.bid_subscribe_detail_id = bsd2.bid_subscribe_detail_id ");
		hqlCount.append(" join ");
		hqlCount.append(" (select  ");
		hqlCount.append(" bsr.bid_subscribe_detail_id ");
		hqlCount.append(" from bid_subscribe_record bsr ");
		hqlCount.append(" where bsr.bid_subscribe_detail_id in ");
		hqlCount.append(" (select ");
		hqlCount.append(" bsd.bid_subscribe_detail_id ");
		hqlCount.append(" from bid_subscribe_detail bsd ");
		hqlCount.append(" left join bid_subscribe bs on bsd.bid_subscribe_id = bs.bid_subscribe_id ");
		hqlCount.append(" where bsd.bid_subscribe_id = :bidSubscribeId ");
		hqlCount.append(" and bsd.subscribe_date >= bs.sub_start_date and bsd.subscribe_date <= bs.sub_end_date ");
		hqlCount.append(" and bsd.subscribe_starttime >= bs.process_start_time_one ");
		hqlCount.append(" and bsd.subscribe_endtime <= bs.process_end_time_one)) demo ");
		hqlCount.append(" on bsr2.bid_subscribe_detail_id = demo.bid_subscribe_detail_id ");
		hqlCount.append(" left join std_compinfo sc on bsr2.comp_id = sc.comp_id where 1=1 ");
		
		if(null != bidSubscribeDetail.getSubscribeDate()){
			hql.append(" and bsd2.subscribe_date = :subscribeDate ");
			hqlCount.append(" and bsd2.subscribe_date = :subscribeDate ");
		}
		
		Query queryCount = getSession().createSQLQuery(hqlCount.toString());
		queryCount.setString("bidSubscribeId", bidSubscribeDetail.getBidSubscribeId());
		if(null != bidSubscribeDetail.getSubscribeDate()){
			queryCount.setDate("subscribeDate", bidSubscribeDetail.getSubscribeDate());
		}
		
		page.setRecords(Long.valueOf(queryCount.uniqueResult().toString()));
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("bidSubscribeId", bidSubscribeDetail.getBidSubscribeId());
		if(null != bidSubscribeDetail.getSubscribeDate()){
			query.setDate("subscribeDate", bidSubscribeDetail.getSubscribeDate());
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		
		return page;
	}
	 
	 
	 public Pagination getSubCompTimeTwoList(BidSubscribeDetail bidSubscribeDetail) {
		 Pagination page = new Pagination(); 
			StringBuffer hql = new StringBuffer();
			StringBuffer hqlCount = new StringBuffer();
			hql.append(" select  ");
			hql.append(" bsr2.comp_id compId,sc.comp_name compName, ");
			hql.append("  DATE_FORMAT(bsd2.subscribe_date,'%Y-%m-%d') subscribeDate, ");
			hql.append(" bsd2.subscribe_starttime subscribeStarttimeStr, ");
			hql.append(" bsd2.subscribe_endtime subscribeEndtimeStr ");
			hql.append(" from bid_subscribe_record bsr2 ");
			hql.append(" join bid_subscribe_detail bsd2 on bsr2.bid_subscribe_detail_id = bsd2.bid_subscribe_detail_id ");
			hql.append(" join ");
			hql.append(" (select  ");
			hql.append(" bsr.bid_subscribe_detail_id ");
			hql.append(" from bid_subscribe_record bsr ");
			hql.append(" where bsr.bid_subscribe_detail_id in ");
			hql.append(" (select ");
			hql.append(" bsd.bid_subscribe_detail_id ");
			hql.append(" from bid_subscribe_detail bsd ");
			hql.append(" left join bid_subscribe bs on bsd.bid_subscribe_id = bs.bid_subscribe_id ");
			hql.append(" where bsd.bid_subscribe_id = :bidSubscribeId ");
			hql.append(" and bsd.subscribe_date >= bs.sub_start_date and bsd.subscribe_date <= bs.sub_end_date ");
			hql.append(" and bsd.subscribe_starttime >= bs.process_start_time_Two ");
			hql.append(" and bsd.subscribe_endtime <= bs.process_end_time_Two)) demo ");
			hql.append(" on bsr2.bid_subscribe_detail_id = demo.bid_subscribe_detail_id ");
			hql.append(" left join std_compinfo sc on bsr2.comp_id = sc.comp_id where 1=1 ");
			
			hqlCount.append(" select  ");
			hqlCount.append(" count(*) ");
			hqlCount.append(" from bid_subscribe_record bsr2 ");
			hqlCount.append(" join bid_subscribe_detail bsd2 on bsr2.bid_subscribe_detail_id = bsd2.bid_subscribe_detail_id ");
			hqlCount.append(" join ");
			hqlCount.append(" (select  ");
			hqlCount.append(" bsr.bid_subscribe_detail_id ");
			hqlCount.append(" from bid_subscribe_record bsr ");
			hqlCount.append(" where bsr.bid_subscribe_detail_id in ");
			hqlCount.append(" (select ");
			hqlCount.append(" bsd.bid_subscribe_detail_id ");
			hqlCount.append(" from bid_subscribe_detail bsd ");
			hqlCount.append(" left join bid_subscribe bs on bsd.bid_subscribe_id = bs.bid_subscribe_id ");
			hqlCount.append(" where bsd.bid_subscribe_id = :bidSubscribeId ");
			hqlCount.append(" and bsd.subscribe_date >= bs.sub_start_date and bsd.subscribe_date <= bs.sub_end_date ");
			hqlCount.append(" and bsd.subscribe_starttime >= bs.process_start_time_Two ");
			hqlCount.append(" and bsd.subscribe_endtime <= bs.process_end_time_Two)) demo ");
			hqlCount.append(" on bsr2.bid_subscribe_detail_id = demo.bid_subscribe_detail_id ");
			hqlCount.append(" left join std_compinfo sc on bsr2.comp_id = sc.comp_id where 1=1 ");
			
			if(null != bidSubscribeDetail.getSubscribeDate()){
				hql.append(" and bsd2.subscribe_date = :subscribeDate ");
				hqlCount.append(" and bsd2.subscribe_date = :subscribeDate ");
			}
			
			Query queryCount = getSession().createSQLQuery(hqlCount.toString());
			queryCount.setString("bidSubscribeId", bidSubscribeDetail.getBidSubscribeId());
			if(null != bidSubscribeDetail.getSubscribeDate()){
				queryCount.setDate("subscribeDate", bidSubscribeDetail.getSubscribeDate());
			}
			
			page.setRecords(Long.valueOf(queryCount.uniqueResult().toString()));
			Query query = getSession().createSQLQuery(hql.toString());
			query.setString("bidSubscribeId", bidSubscribeDetail.getBidSubscribeId());
			if(null != bidSubscribeDetail.getSubscribeDate()){
				query.setDate("subscribeDate", bidSubscribeDetail.getSubscribeDate());
			}
			query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			query.setFirstResult((page.getPage()-1)*page.getMaxResults());
			query.setMaxResults(page.getMaxResults());
			page.setRows(query.list());
			
			return page;
	}
	 /**
	  * @category 预约情况查看
	  * @author 韩守松
	  * @date   2015年9月17日
	  * @param  @return
	  */
	 public Pagination getCompRecord(Pagination page,BidSubscribeDetail bidSubscribeDetail){
		 StringBuffer hql = new StringBuffer();
		 StringBuffer countHql = new StringBuffer();
		 hql.append(" select ");
		 hql.append(" bsr.bid_subscribe_record_id bidSubscribeRecordId,");
		 hql.append(" bsr.process_status processStatus,");
		 hql.append(" DATE_FORMAT(bsr.process_datetime,'%Y-%m-%d %H:%i:%S') processDatetime, ");
		 hql.append(" bsr.comp_id compId, ");
		 hql.append(" sc.comp_name compName, ");
		 hql.append(" DATE_FORMAT(bsd.subscribe_date,'%Y-%m-%d') subscribeDateStr, ");
		 hql.append(" DATE_FORMAT(bsd.subscribe_starttime,'%H:%i:%S') subscribeStarttime, ");
		 hql.append(" DATE_FORMAT(bsd.subscribe_endtime,'%H:%i:%S') subscribeEndtime ");
		 hql.append(" from bid_subscribe_record bsr  ");
		 hql.append(" left join std_compinfo sc on bsr.comp_id = sc.comp_id ");
		 hql.append(" left join bid_subscribe_detail bsd on bsr.bid_subscribe_detail_id = bsd.bid_subscribe_detail_id ");
		 hql.append(" left join bid_subscribe bs on bsd.bid_subscribe_id = bs.bid_subscribe_id ");
		 hql.append("  where bs.proj_id = :projId ");
		 
		 countHql.append(" select ");
		 countHql.append(" count(*) ");
		 countHql.append(" from bid_subscribe_record bsr  ");
		 countHql.append(" left join std_compinfo sc on bsr.comp_id = sc.comp_id ");
		 countHql.append(" left join bid_subscribe_detail bsd on bsr.bid_subscribe_detail_id = bsd.bid_subscribe_detail_id ");
		 countHql.append(" left join bid_subscribe bs on bsd.bid_subscribe_id = bs.bid_subscribe_id ");
		 countHql.append("  where bs.proj_id = :projId ");
		 
		 if(null != bidSubscribeDetail.getSubscribeDate()){
			 hql.append(" and  bsd.subscribe_date = :subscribeDate ");
			 countHql.append(" and  bsd.subscribe_date = :subscribeDate ");
		 }
		
		 hql.append("order by bsr.upd_datetime desc");
		 Query queryCount = getSession().createSQLQuery(countHql.toString());
		 queryCount.setString("projId", bidSubscribeDetail.getProjId());
		 if(null != bidSubscribeDetail.getSubscribeDate()){
			 queryCount.setDate("subscribeDate", bidSubscribeDetail.getSubscribeDate());
		 }
		 page.setRecords(Long.valueOf(queryCount.uniqueResult().toString()));
		 
		 Query query = getSession().createSQLQuery(hql.toString());
		 query.setString("projId", bidSubscribeDetail.getProjId());
		 if(null != bidSubscribeDetail.getSubscribeDate()){
			 query.setDate("subscribeDate", bidSubscribeDetail.getSubscribeDate());
		 }
		 query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		 query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		 query.setMaxResults(page.getMaxResults());
		 page.setRows(query.list());
		 
		 return page;
	 }
	 
	 /**
	  * @category 根据项目Id 获取相关预约信息
	  * @author 韩守松
	  * @date   2015年9月28日
	  * @param  @param projId
	  * @param  @return
	  */
	 public BidSubscribe  getIsSubscribe(String projId){
		 StringBuffer hql = new StringBuffer();
		 hql.append(" select count(*) isSet,  ");
		 hql.append(" bs.is_enable isEnable, ");
		 hql.append(" bs.bid_subscribe_id bidSubscribeId ");
		 hql.append(" from bid_subscribe bs ");
		 hql.append(" inner join  bid_project bp on bs.proj_id = bp.proj_id ");
		 hql.append(" where bp.proj_id = :projId ");
		 hql.append(" and bp.status = 1");
		 
		 Query query = getSession().createSQLQuery(hql.toString());
		 query.setString("projId", projId);
		 BidSubscribe bidSubscribe = new BidSubscribe();
		 Object[] obj = (Object[]) query.uniqueResult();
		 if(obj.length > 0){
			 bidSubscribe.setIsSet(obj[0]==null?"":obj[0].toString());
			 bidSubscribe.setIsEnable(obj[1]==null?0:Integer.valueOf(obj[1].toString()));
			 bidSubscribe.setBidSubscribeId(obj[2]==null?"":obj[2].toString());
		 }
		 return bidSubscribe;
	 }
}
